﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SchoolManagementSystem
{
    public partial class MuridFilter : Form
    {
        string sql = string.Empty;
        string where = string.Empty;
        string join = string.Empty;
        bool startWhere = false; //where has contained condition or not

        private enum DataType { String, Number, Date }

        public MuridFilter()
        {
            InitializeComponent();
        }

        public string SQL { get { return this.sql + this.join + this.where; } }

        private void InitializeQuery()
        {
            this.sql = "SELECT s.* FROM Student AS s";
            this.where = string.Empty;
            this.join = string.Empty;
            this.startWhere = false;
        }

        
        private void insertWhere(string column, string value, DataType type)
        {
            if (this.where == string.Empty) this.where += " WHERE ";
            if (this.startWhere) this.where += " AND ";

            if (type == DataType.String) this.where += (column + " = '" + value + "'");
            else if (type == DataType.Number) this.where += (column + " = " + value);
            
            startWhere = true;
        }

        private void insertWhereLike(string column, string value)
        {
            if (this.where == string.Empty) this.where += " WHERE ";
            if (this.startWhere) this.where += " AND ";

            this.where += (column + " like '%" + value + "%'");
            startWhere = true;
        }

        private void insertWhereOperator(string column, string value, string op)
        {
            insertWhereOperator(column, value, op, DataType.Number);
        }

        private void insertWhereOperator(string column, string value, string op, DataType type)
        {
            if (this.where == string.Empty) this.where += " WHERE ";
            if (this.startWhere) this.where += " AND ";

            if (type == DataType.Number) this.where += (column + " " + op + " " + value);
            else this.where += (column + " " + op + " '" + value + "'");
            startWhere = true;
        }

        private void insertJoin(string table, string onJoin)
        {
            this.join += (" INNER JOIN " + table + " ON " + onJoin);
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            InitializeQuery();

            // student status
            if (!rbRegistered.Checked)
            {
                // validate number
                int year1, year2;
                try
                {
                    year1 = int.Parse(txtStatus1.Text);
                    year2 = int.Parse(txtStatus2.Text);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Data tahun hanya menerima input angka saja", "Peringatan!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                }

                if (rbMutation.Checked)
                {
                    insertWhereOperator("s.mutation_date", year1.ToString() + "-1-1", ">=", DataType.Date);
                    insertWhereOperator("s.mutation_date", year2.ToString() + "-12-31", "<=", DataType.Date);
                }
                else if (rbGraduate.Checked)
                {
                    if (year1 == year2)
                        insertWhere("s.graduate_year", year1.ToString(), DataType.Number);
                    else
                    {
                        insertWhereOperator("s.graduate_year", year1.ToString(), ">=");
                        insertWhereOperator("s.graduate_year", year2.ToString(), "<=");
                    }
                }
            }

            // student birthday
            if (chkBirthday.Checked)
            {
                if (dtpBirthday1.Value == dtpBirthday2.Value) insertWhere("s.stu_birthdate", dtpBirthday1.Value.ToString("yyyy-MM-dd"), DataType.String);
                else
                {
                    insertWhereOperator("s.stu_birthdate", dtpBirthday1.Value.ToString("yyyy-MM-dd"), ">=", DataType.Date);
                    insertWhereOperator("s.stu_birthdate", dtpBirthday2.Value.ToString("yyyy-MM-dd"), "<=", DataType.Date);
                }
            }

            // student gender
            if (cmbGender.SelectedIndex > 0)
            {
                if (cmbGender.SelectedIndex == 1) insertWhere("s.stu_gender", "L", DataType.String);
                else if (cmbGender.SelectedIndex == 2) insertWhere("s.stu_gender", "P", DataType.String);
            }

            // student id
            if (txtID.Text != string.Empty) insertWhereLike("s.stu_id", txtID.Text);

            // student name
            if (txtSName.Text != string.Empty) insertWhereLike("s.stu_name", txtSName.Text);

            bool hasParent = false;

            // parent name
            if (txtPName.Text != string.Empty)
            {
                insertWhereLike("p.par_name", txtPName.Text);
                hasParent = true;
            }

            // parent occupation
            if (txtOccupation.Text != string.Empty)
            {
                insertWhereLike("p.par_job", txtOccupation.Text);
                hasParent = true;
            }

            // parent address
            if (txtAddress.Text != string.Empty)
            {
                insertWhereLike("p.par_address", txtAddress.Text);
                hasParent = true;
            }

            if (hasParent) insertJoin("Parent as p", "s.par_id=p.par_id");

            DialogResult = DialogResult.OK;
            this.Close();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            DialogResult = DialogResult.Cancel;
            this.Close();
        }

        private void statusChecked()
        {
            if (rbRegistered.Checked)
            {
                lblYear.Enabled = false;
                lblStatus.Enabled = false;
                txtStatus1.Enabled = false;
                txtStatus2.Enabled = false;

                txtStatus1.Text = string.Empty;
                txtStatus2.Text = string.Empty;
            }
            else
            {
                lblYear.Enabled = true;
                lblStatus.Enabled = true;
                txtStatus1.Enabled = true;
                txtStatus2.Enabled = true;
            }
        }

        private void rbRegistered_CheckedChanged(object sender, EventArgs e)
        {
            statusChecked();
        }

        private void rbMutation_CheckedChanged(object sender, EventArgs e)
        {
            statusChecked();
        }

        private void rbGraduate_CheckedChanged(object sender, EventArgs e)
        {
            statusChecked();
        }

        private void chkBirthday_CheckedChanged(object sender, EventArgs e)
        {
            CheckBox check = (CheckBox)sender;
            if (check.Checked)
            {
                dtpBirthday1.Enabled = true;
                lblBirthday.Enabled = true;
                dtpBirthday2.Enabled = true;
            }
            else
            {
                dtpBirthday1.Enabled = false;
                lblBirthday.Enabled = false;
                dtpBirthday2.Enabled = false;
            }
        }
    }
}
